package com.itchen.school.common.utils;

import com.google.common.collect.Lists;
import com.itchen.school.common.exception.SysException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.List;

/**
 * excel 工具
 */
public class ExcelHelper {

    private static final Logger log = LoggerFactory.getLogger(ExcelHelper.class);

    private Workbook _workbook;

    // 97 是 office 97 格式, 非 97 是指定 2003 格式
    private static int FORMAT_97 = 97;

    private static int FORMAT_N97 = 98;

    // region Constructors
    public ExcelHelper() {
        _workbook = new XSSFWorkbook();
    }

    public ExcelHelper(InputStream stream) throws IOException {
        _workbook = new HSSFWorkbook(stream);
        stream.close();
    }

    public ExcelHelper(int dwFormat) {
        if (dwFormat != FORMAT_97) dwFormat = FORMAT_N97;
        if (dwFormat == FORMAT_97) {
            _workbook = new HSSFWorkbook();
        } else {
            _workbook = new XSSFWorkbook();
        }
    }

    public ExcelHelper(InputStream stream, int dwFormat) throws IOException {
        if (dwFormat != FORMAT_97) dwFormat = FORMAT_N97;
        if (dwFormat == FORMAT_97) {
            _workbook = new HSSFWorkbook(stream);
        } else {
            _workbook = new XSSFWorkbook(stream);
        }
        stream.close();
    }

    public ExcelHelper(String excelFullPath) throws IOException {
        InputStream stream = Files.newInputStream(Paths.get(excelFullPath), StandardOpenOption.CREATE);
        String fileExt = FileUtils.getExtensionName(excelFullPath);
        if ("xls".equals(fileExt)) {
            _workbook = new HSSFWorkbook(stream);
        } else {
            _workbook = new XSSFWorkbook(stream);
        }
        stream.close();
    }

    public ExcelHelper(File file) throws IOException {
        log.info("导入文件是否存在：" + (file.exists() ? "是" : "否"));
        if (file.exists()) {
            log.info("导入文件名称：" + file.getName());
            log.info("导入文件大小：" + file.length());
        }
        InputStream stream = Files.newInputStream(file.toPath(), StandardOpenOption.CREATE);
        String fileExt = FileUtils.getExtensionName(file.getName());
        if ("xls".equals(fileExt)) {
            //.xls格式
            _workbook = new HSSFWorkbook(stream);
        } else {
            //.xlsx格式
            _workbook = new XSSFWorkbook(stream);
        }
        stream.close();
    }
    // endregion

    /***
     *
     * @param sheetIndex  sheet 索引, 基于 0
     * @param headerRowIndex  列头索引
     * @param cellIndex 数据开始列, 至少要 1, 第 0 行是列頭
     * @param cellIndexEnd 数据结束咧, -1 表示全部列
     * @return 對象列表
     */
    public List<String> getHeadRows(int sheetIndex, int headerRowIndex, int cellIndex, int cellIndexEnd) {
        Sheet pSheet = _workbook.getSheetAt(sheetIndex);
        Row pRow = pSheet.getRow(headerRowIndex);
        int cellLastIndex = pRow.getLastCellNum();
        if (cellLastIndex < cellIndex) {
            throw new SysException("开始列大于excel最大列数");
        }
        if (cellIndexEnd == -1) {
            cellIndexEnd = cellLastIndex;
        }
        if (cellLastIndex < cellIndexEnd) {
            throw new SysException("最终列大于excel最大列数");
        }
        List<String> list = Lists.newArrayList();
        for (int i = cellIndex; i < cellIndexEnd; i++) {
            list.add(String.valueOf(getCellValue(pRow, i)));
        }

        return list;
    }

    public static Object getCellValue(Row row, int cellIndex){
        Cell cell=row.getCell(cellIndex);
        return getCellValue(cell);
    }

    public static Object getCellValue(Cell cell) {
        if(cell ==null) return null;
        // cell.getCellTypeEnum()
        switch(cell.getCellType()){
            case STRING:
            case BLANK: return cell.getStringCellValue();
            case NUMERIC: return cell.getNumericCellValue();
            case FORMULA: return cell.getCellFormula();
            case BOOLEAN: return cell.getBooleanCellValue();
        }
        Object v= cell.getDateCellValue();
        if(v==null) v= cell.getRichStringCellValue();
        if(v==null) v= cell.getErrorCellValue();
        return v;
    }

}
